package com.ukefu.webim.service.repository;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.action.search.SearchRequestBuilder;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.query.QueryStringQueryBuilder;
import org.elasticsearch.index.query.QueryStringQueryBuilder.Operator;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.search.aggregations.AggregationBuilder;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.Terms.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidDataSource;
import com.ukefu.core.UKDataContext;
import com.ukefu.core.UKDataContext.FilterConValueType;
import com.ukefu.util.UKTools;
import com.ukefu.util.bi.ReportData;
import com.ukefu.util.bi.model.Level;
import com.ukefu.util.bi.model.ValueData;
import com.ukefu.webim.service.cache.CacheHelper;
import com.ukefu.webim.util.CallCenterUtils;
import com.ukefu.webim.web.model.ChartProperties;
import com.ukefu.webim.web.model.ColumnProperties;
import com.ukefu.webim.web.model.Cube;
import com.ukefu.webim.web.model.CubeLevel;
import com.ukefu.webim.web.model.CubeMeasure;
import com.ukefu.webim.web.model.CubeMetadata;
import com.ukefu.webim.web.model.Dimension;
import com.ukefu.webim.web.model.MetadataTable;
import com.ukefu.webim.web.model.PublishedCube;
import com.ukefu.webim.web.model.ReportFilter;
import com.ukefu.webim.web.model.ReportModel;
import com.ukefu.webim.web.model.SysDic;
import com.ukefu.webim.web.model.TableProperties;
import com.ukefu.webim.web.model.Template;
import com.ukefu.webim.web.model.UKeFuDic;
import com.ukefu.webim.web.model.User;

import freemarker.template.TemplateException;

@Service("reportCubeService")
public class ReportCubeService{
	
	@Autowired
	private TemplateRepository templateRes;
	
	@Value("${web.upload-path}")
    private String path;
	
	@Autowired
	private DataSourceService dataSource ;
	
	@Autowired
	private ReportFilterRepository  reportFilterRes;
	
	@Autowired
	private MetadataRepository metadataRes;
	
	@Autowired
	private TablePropertiesRepository tablePropertiesRes;
	
	@Autowired
    private DruidDataSource druidDataSource;
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	/**
	 * 
	 * @param report
	 * @return
	 * @throws Exception 
	 */
	public ReportData getReportData(ReportModel model, Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap) throws Exception {
		return getReportData(model, true , cube, request, parseParam, semap) ;
	}
	
	/**
	 * 
	 * @param report
	 * @return
	 * @throws Exception 
	 */
	public ReportData getReportData(ReportModel model, boolean linkdata,Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap) throws Exception {
		ReportData reportData = null ;
		if(model.getReportData() == null) {
			if(cube!=null && "cache".equals(cube.getModeltype())) {
				reportData = getCacheReportData(model, cube, request, parseParam, semap)  ;
			}else if(cube!=null && "estable".equals(cube.getModeltype())) {
				reportData = getESCubeReportData(model, cube, request, parseParam, semap)  ;
			}else if(cube!=null && "table".equals(cube.getModeltype())) {
				reportData = getSqlCubeReportData(model, cube, request, parseParam, semap)  ;
			}else {
				reportData = getCubeReportData(model, cube, request, parseParam, semap)  ;
			}
			if(linkdata == true) {
				ChartProperties properties = model.getChartProperties() ;
				if(properties!=null && !StringUtils.isBlank(properties.getLinkdatajson())) {
					//
					String[] mls = properties.getLinkdatajson().split(",") ;
					List<String> ids = new ArrayList<String>();
					for(String ml : mls) {
						if(!StringUtils.isBlank(ml) && !ml.equals(model.getId())) {
							ids.add(ml) ;
						}
					} 
					if(mls!=null && mls.length > 0) {
						for(String ml : mls) {
							ReportModel	md = getModel(ml, model.getOrgi()) ;
							if(md!=null) {
								/**
								 * 避免循环调用
								 */
								md.setReportData(this.getReportData(md, false, getCube(md), request, parseParam, semap));
								
								/**
								 * 合并数据
								 */
								ChartProperties mdProperties = md.getChartProperties() ;
								if(mdProperties!=null) {
									Level root = reportData.getRow() ;
									Level mdRoot = md.getReportData().getRow() ; 
									if (mdRoot.getRowspan()==0) {
										mdRoot.setRowspan(1);
									}
									root.setRowspan(root.getRowspan()+mdRoot.getRowspan());
									ReportData temp = reportData ;
									if(root.getTitle()!=null && root.getTitle().size() < mdRoot.getTitle().size()) {
										reportData = md.getReportData() ;
										md.setReportData(temp);
										root = reportData.getRow() ;
										mdRoot = temp.getRow() ; 
									}
									if(mdRoot.getChilderen() == null) {
										Level totalLevel = new Level("合计", "row" , root, 1) ;
										reportData.getData().addAll(processMeasure(md.getReportData().getData() , md.getMeasures())) ;
										if(mdProperties.isLinkdata() && !StringUtils.isBlank(mdProperties.getLinkdatatitle())) {
											totalLevel.setName(mdProperties.getLinkdatatitle());
										}
										if(mdProperties.getLinkcolspan() > 0) {
											totalLevel.setColspan(mdProperties.getLinkcolspan());
										}else {
											totalLevel.setColspan(root.getTitle().size());
										}
										totalLevel.setRowspan(1);
										totalLevel.setValueData(md.getReportData().getData().get(0));
										root.getChilderen().add(totalLevel) ;
										if (root.getTitle()!=null && root.getTitle().size()==0) {
											List<Level> rti = new ArrayList<Level>();
											root.getTitle().add(rti);
										}
										root.getTitle().get(0).add(totalLevel) ;
									}else if(root.getTitle().size() >= mdRoot.getTitle().size()){
										root.getChilderen().addAll(mdRoot.getChilderen()) ;
										for(int i =0 ; i<root.getTitle().size() ; i++) {
											if (i<mdRoot.getTitle().size()) {
												root.getTitle().get(i).addAll(mdRoot.getTitle().get(i)) ;
											}
										}
										reportData.getData().addAll(processMeasure(md.getReportData().getData() , md.getMeasures())) ;
									}
								}
							}
						}
					}
				}
			}
			model.setReportData(reportData);
		}else {
			reportData = model.getReportData() ;
		}
		return reportData ;
	}
	
	/**
	 * 从缓存获取数据
	 * @param model
	 * @param cube
	 * @param request
	 * @param parseParam
	 * @param semap
	 * @return
	 * @throws Exception
	 */
	private ReportData getCacheReportData(ReportModel model , Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap)  throws Exception{
		return new CacheService().execute(model, cube) ;
	}
	
	/**
	 * 
	 * @param model
	 * @return
	 */
	private Cube getCube(ReportModel model) {
		Cube cube = null ;
		PublishedCubeRepository publishedCubeRepository = UKDataContext.getContext().getBean(PublishedCubeRepository.class) ;
		PublishedCube publishedCube = publishedCubeRepository.findById(model.getPublishedcubeid());
		if(publishedCube!=null) {
			cube = publishedCube.getCube() ;
		}
		return cube ;
	}
	
	/**
	 * 从立方体和 数据表获取数据
	 * @param model
	 * @param cube
	 * @param request
	 * @param parseParam
	 * @param semap
	 * @return
	 * @throws Exception
	 */
	private ReportData getCubeReportData(ReportModel model , Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap)  throws Exception{
		processFilter(model, cube, request);
		String orgi = UKDataContext.SYSTEM_ORGI;

		Template modeltp =  templateRes.findByIdAndOrgi(model.getTempletid(),orgi);

		boolean isTable = modeltp!=null&&"数据表".equals(modeltp.getName());
		
		cube.setSql(createCubeSQL(model, cube, request , true,semap)) ;
		
		Template tp = null;
		
		List<SysDic> tpDicList = UKeFuDic.getInstance().getDic(UKDataContext.UKEFU_SYSTEM_DIC) ;
    	for(SysDic sysDic : tpDicList) {
    		if(sysDic.getCode().equals("reportquery")) {
    			 List<Template> tpList = templateRes.findByTemplettypeAndOrgi(sysDic.getId(), orgi);
    			 for(Template tpl : tpList) {
    				 if("reportquery".equals(tpl.getCode())) {
    					 tp = tpl;
    					 break;
    				 }
    			 }
    		}
   		}
		Map<String,Object> tplValuesMap = new HashMap<>();
		tplValuesMap.put("reportModel", model);
		tplValuesMap.put("cube", cube);
		tplValuesMap.put("name", model.getName());
		tplValuesMap.put("istable", isTable);
		CubeService cubeService= new CubeService(tp.getTemplettext(), path, dataSource , tplValuesMap,true);
		String mdx = this.genMdx(model, cube,isTable,request);
		ReportData reportData = cubeService.execute(mdx , model.getMeasures() , model.getProperties()) ;
		if("true".equals(model.getIsloadfulldata())) {
			int p = Integer
					.parseInt(request.getParameter("p") != null
							&& request.getParameter("p").matches(
									"[\\d]{1,}") ? request
							.getParameter("p") : "1");
			int ps = Integer
					.parseInt(request.getParameter("ps") != null
							&& request.getParameter("ps").matches(
									"[\\d]{1,}") ? request
							.getParameter("ps") : "0");
			if (p < 1) {
				p = 1;
			}
			if (ps < 1) {
				ps = model.getPagesize()>0 ? model.getPagesize() : 20;
			}
			reportData.setPage(p);
			reportData.setPageSize(ps);
		}
		
		return reportData;
	}
	
	
	/**
	 * 生成结算数据SQL
	 * @param model
	 * @param cube
	 * @param request
	 * @param queryLog
	 * @return
	 * @throws Exception 
	 */
	public String createCubeSQL(ReportModel model, Cube cube,
			HttpServletRequest request  , boolean useStaticFilter, HashMap<String,String> semap) throws Exception{
		StringBuffer strb = new StringBuffer() ;
		strb.append("select ");
		//要查询的表名以及左连接
		StringBuffer tables = new StringBuffer() ;
		StringBuffer mainTable = new StringBuffer(" from ");
		Map<String, Object> values = new HashMap<String,Object>();
		if(request.getParameterNames()!=null) {
			Enumeration<String> iterator = request.getParameterNames() ;
			while(iterator.hasMoreElements()) {
				String param = iterator.nextElement() ;
				values.put(param, request.getParameter(param)) ;
			}
		}
		String mainTableStr = null;
		Map<String,String> tableMap = new HashMap<>();
		int index = 1;
		Map<String,TableProperties> tableppyMap = new HashMap<>();
		Map<String,MetadataTable> tableObjMap = new HashMap<>();
		CubeMetadata mainMetaData = null ;
		for(CubeMetadata cm:cube.getMetadata()) {
			tableMap.put(cm.getTb().getId(), "a"+index);
			if("0".equals(cm.getMtype())) {
				mainMetaData = cm ;
				if(cm.getTb().getTabletype().equals("1")) {
					mainTable.append(cm.getTb().getTablename());
				}else if(cm.getTb().getTabletype().equals("2")) {
					mainTable.append("(").append(UKTools.getTemplet(cm.getTb().getDatasql(), values)).append(")");
				}
				mainTable.append("  ").append(tableMap.get(cm.getTb().getId())).append(" ");
				mainTableStr = cm.getTb().getId();
			}
			List<TableProperties> tpList = tablePropertiesRes.findByDbtableid(cm.getTb().getId()) ;
			for(TableProperties tableproperty:tpList) {
				tableppyMap.put(tableproperty.getId(),tableproperty);
			}
			tableObjMap.put(cm.getTb().getId(),cm.getTb());
			index++;
		}
		//要查询的列名
		Map<String,String> exist = new HashMap<String,String>();
		StringBuffer columns = new StringBuffer() ;
		for(Dimension d:cube.getDimension()) {
			for(CubeLevel cl : d.getCubeLevel()) {
				if(!mainMetaData.getTb().getTablename().equals(cl.getTablename())) {
					if(columns.length() > 0) {
						columns.append(",");
					}
					columns.append(tableMap.get(cl.getTableproperty().getDbtableid())).append(".").append(cl.getTableproperty().getFieldname()).append(" as ").append(cl.getTableproperty().getKey());
					exist.put(cl.getColumname(), cl.getColumname()) ;
				}
			}
		}
		
		//要查询的表名以及左连接
		for(CubeMetadata cm:cube.getMetadata()) {
			if(!"0".equals(cm.getMtype())) {
				for(Dimension d:cube.getDimension()) {
					if(!StringUtils.isBlank(d.getFkfield())&&!StringUtils.isBlank(d.getFktable())&&!StringUtils.isBlank(d.getFktableid())&&cm.getTb().getId().equals(d.getFktable())) {
						tables.append(" left join ").append(cm.getTb().getTablename()).append(" as ").append(tableMap.get(cm.getTb().getId())).append(" on ");
						StringBuffer onsql = new StringBuffer(); 
						onsql.append(tableMap.get(mainTableStr)).append(".").append(tableppyMap.get(d.getFkfield()).getFieldname());
						onsql.append("=").append(tableMap.get(d.getFktable())).append(".").append(tableppyMap.get(d.getFktableid()).getFieldname());
						onsql.append(" ");
						tables.append(onsql);
					}
					
				}
			}else {
				
			}
		}
		if(mainMetaData!=null) {
			strb.append(" ").append(tableMap.get(mainMetaData.getTb().getId())).append(".* ");
			if(columns.length() > 0) {
				strb.append(",").append(columns.toString()) ;
			}
			strb.append(" ").append(mainTable).append(tables);
		}else {
			strb.append(" * ").append(mainTable).append(tables);
		}
		//过滤关联的表名以及左连接
		StringBuffer filtertables = new StringBuffer() ;
		StringBuffer wherecon = new StringBuffer(" 1=1 ");
		List<ReportFilter> reportFilter = model.getFilters() ;
		Map<String,String> tablefilterMap = new HashMap<>();
		List<String> organList  = new ArrayList<>();
		if( !reportFilter.isEmpty()){
			//左链接 过滤器关联的表
			index = 1;
			for(ReportFilter filter : reportFilter){
				tablefilterMap.put(filter.getFktableid()+filter.getId(), "b"+index);
				index++;
			}
			for(ReportFilter filter : reportFilter){
				if(tableMap.get(filter.getFktableid()) == null) {
					filtertables.append(" left join ").append(tableObjMap.get(filter.getFktableid()).getTablename()).append(" as ").append(tablefilterMap.get(filter.getFktableid()+filter.getId())).append(" on ");
					StringBuffer onsql = new StringBuffer(); 
					onsql.append(tableMap.get(mainTableStr)).append(".").append(tableppyMap.get(filter.getFieldid()).getFieldname());
					onsql.append("=").append(tablefilterMap.get(filter.getFktableid()+filter.getId())).append(".").append(tableppyMap.get(filter.getFkfieldid()).getFieldname());
					onsql.append(" ");
					filtertables.append(onsql);
				}
				if("range".equals(filter.getValuefiltertype())){//范围   range code_start 和 code_end
					if((!StringUtils.isBlank(filter.getCode()))){
						String startValue = getStartValue(filter , request);
						String endValue = getEndValue(filter , request);
						String dataname = tableMap.get(filter.getFktableid()) + "." + tableppyMap.get(filter.getFilterfieldid()).getFieldname();
						
						if(!StringUtils.isBlank(startValue) ){
							wherecon.append(wherecon.length()>0?" and ":"").append(dataname).append(" >= '").append(startValue).append("' ");
						}
						if(!StringUtils.isBlank(endValue) ){
							wherecon.append(wherecon.length()>0?" and ":"").append(dataname).append(" <= '").append(endValue).append("' ");
						}
					}
				}else{//compare
					String value = getDefaultValue(filter , request);
					String dataname = tableMap.get(filter.getFktableid()) + "." + tableppyMap.get(filter.getFilterfieldid()).getFieldname();
					
					if(!StringUtils.isBlank(value)){
						if(!wherecon.toString().contains(dataname)){
							if(!StringUtils.isBlank(value)){
								if(wherecon.length()>0){
									wherecon.append(" and ");
								}
								if("EQUAL".equals(filter.getComparetype().toUpperCase())){
									if(value.indexOf(",")>-1){
										wherecon.append(dataname).append(" in ('").append(value.replaceAll(",", "','")).append("') ");
									}else{
										wherecon.append(dataname).append(" = '").append(value).append("' ");
									}
								}else if("NOT".equals(filter.getComparetype().toUpperCase())){
									if(value.indexOf(",")>-1){
										wherecon.append(dataname).append(" not in ('").append(value.replaceAll(",", "','")).append("') ");
									}else{
										wherecon.append(dataname).append(" != '").append(value).append("' ");
									}
								}
							}
						}
					}
					
					//数据权限
					if (!StringUtils.isBlank(tableppyMap.get(filter.getFilterfieldid()).getFieldname()) && tableppyMap.get(filter.getFilterfieldid()).getFieldname().toLowerCase().equals("organ") && filter.isDataauth()) {
						User user = getUser(request);
						organList = CallCenterUtils.getExistOrgan(user);
						if (!user.isSuperuser()) {
							if (organList != null && organList.size() > 0) {
								for(int i=0;i<organList.size();i++){
									if (i==0) {
										wherecon.append(" and ").append(dataname).append(" in ('").append(organList.get(i)).append("'");
									}else{
										wherecon.append(",'").append(organList.get(i)).append("'");
									}
									if (i==organList.size()-1) {
										wherecon.append(") ");
									}
								}
							}else{
								wherecon.append(" and ").append(dataname).append(" ='").append(UKDataContext.UKEFU_SYSTEM_NO_DAT).append("' ");
							}
						}
					}
					//
				}
			}
		}
		if(filtertables.length()>0){
			strb.append(filtertables);
		}
		//NOTE 报表查询数据 需要添加orgi字段 区分租户
		boolean isExistOrgi = false;
		List<TableProperties> tableproperty = tablePropertiesRes.findByDbtableid(mainMetaData.getTb().getId());
		if(tableproperty != null && tableproperty.size() > 0){
			for(TableProperties tbp : tableproperty){
				if("orgi".equalsIgnoreCase(tbp.getFieldname())){
					isExistOrgi = true;
					break;
				}
			}
		}
		if(isExistOrgi){
			wherecon.append(" and ").append(tableMap.get(mainMetaData.getTb().getId())).append(".orgi='").append(mainMetaData.getOrgi() + "' ");
		}

		if(wherecon.length()>0){
			strb.append(" where ").append(wherecon);
		}
		if(mainMetaData!=null && !StringUtils.isBlank(mainMetaData.getParameters())) {
			strb.append(" and (").append(mainMetaData.getParameters()).append(") ");
		}
		return strb.length()>0 ? strb.toString() : null ;
	}
	public boolean checkSemap(String fieldename,CubeMetadata mainMetaData){
		if (mainMetaData != null && mainMetaData.getTb() != null) {
			List<TableProperties> mainlist = tablePropertiesRes.findByDbtableid(mainMetaData.getTb().getId());
			for(TableProperties tableProperties : mainlist){
				if(tableProperties.getFieldname().equals(fieldename)){
					return true;
				}
			}
		}
		
		return false;
	}
	
	public String genMdx(ReportModel model, Cube cube,boolean isTable,HttpServletRequest request ) {
		StringBuffer rowstrb = new StringBuffer(), colstrb = new StringBuffer();
		StringBuffer dimstrb= new StringBuffer();
		StringBuffer coldimstrb= new StringBuffer();
		CubeLevel last = null ; 
		//维度 只需要最后一个
		if(!model.getProperties().isEmpty()) {
			ColumnProperties cp = model.getProperties().get(model.getProperties().size()-1);
			dimstrb.append("{") ;
			
			all:for(Dimension d:cube.getDimension()) {
				for(CubeLevel cl:d.getCubeLevel()) {
					if(cp.getDataid().equals(cl.getId())) {
						if("desc".equals(cp.getSorttype())) {
							dimstrb.append("Order ( [").append(model.getName()).append("].[").append(cl.getName()).append("].members , ").append("[").append(model.getName()).append("].[").append(cl.getName()).append("].CurrentMember.name)");
						}else {
							dimstrb.append("[").append(model.getName()).append("].[").append(cl.getName()).append("].members ");
						}
						last = cl ;
						
						//指标排序
						if (dimstrb.toString().indexOf("Order")<0) {
							for(ColumnProperties cpsor : model.getMeasures()) {
								if(!StringUtils.isBlank(cpsor.getSorttype())) {
									dimstrb.insert(1, "Order ( ");
									dimstrb.append(", ").append("[Measures].[").append(cpsor.getTitle()).append("],").append(cpsor.getSorttype().toUpperCase()).append(")");
									break;
								}
							}
						}
						if(cp.isUnresize()) {
							dimstrb.append(",[").append(model.getName()).append("] ");
						}
						break all;
					}
				}
			}
			
			for(ColumnProperties tempCp : model.getProperties()) {
				if(tempCp.isUnresize() && !tempCp.getId().equals(cp.getId())) {
					all:for(Dimension d:cube.getDimension()) {
						for(CubeLevel cl:d.getCubeLevel()) {
							if(tempCp.getDataid().equals(cl.getId())) {
								dimstrb.append(",[").append(model.getName()).append("].[").append(cl.getName()).append("].members ");
								last = cl ;
								break all;
							}
						}
					}
				}
			}
			
			dimstrb.append("}") ;
		}
		if("true".equals(model.getIsloadfulldata())) {
			int p = Integer
					.parseInt(request.getParameter("p") != null
							&& request.getParameter("p").matches(
									"[\\d]{1,}") ? request
							.getParameter("p") : "1");
			int ps = Integer
					.parseInt(request.getParameter("ps") != null
							&& request.getParameter("ps").matches(
									"[\\d]{1,}") ? request
							.getParameter("ps") : "0");
			if (p < 1) {
				p = 1;
			}
			if (ps < 1) {
				ps = model.getPagesize()>0 ? model.getPagesize() : 20;
			}
			int startindex = (p - 1) * ps;
			if(!model.getProperties().isEmpty()) {
				dimstrb.insert(0, "subset( ").append(",").append(startindex).append(" , ").append(ps).append(" ) ") ;
			}
		}
		rowstrb.append(dimstrb).append(" ON ROWS");
		//指标
		StringBuffer measure = new StringBuffer() , fixed = new StringBuffer();
		for(ColumnProperties cp:model.getMeasures()) {
			if(cp.isMgroup()) {
				if(fixed.length() > 0) {
					fixed.append(" , ") ;
				}
				fixed.append(" [Measures].[").append(cp.getTitle()+"_RE_NAME_TITLE").append("]");
			}else {
				if(measure.length() > 0) {
					measure.append(" , ") ;
				}
				measure.append(" [Measures].[").append(cp.getTitle()+"_RE_NAME_TITLE").append("]");
			}
		}
		if(measure.length() > 0) {
			measure.insert(0, " {");
			measure.append(" }");
		}
		if(fixed.length() > 0) {
			fixed.insert(0, " {");
			fixed.append(" }");
		}
		//列维度
		if(isTable) {
			//维度 只需要最后一个
			if(!model.getColproperties().isEmpty()) {
				ColumnProperties cp = model.getColproperties().get(model.getColproperties().size()-1);
				all:for(Dimension d:cube.getDimension()) {
					for(CubeLevel cl:d.getCubeLevel()) {
						if(cp.getDataid().equals(cl.getId())) {
							coldimstrb.append(" [").append(model.getName()).append("_col].[").append(cl.getName()).append("].members ");
							break all;
						}
					}
				}
			}
			if(coldimstrb.length() > 0 ) {
//				colstrb.append("Union(CrossJoin( [数据表_col].[答案].members  ,  {[Measures].[挂断] } ) , CrossJoin( [数据表_col].[答案].members  ,  { [Measures].[占比]  } ))").append(" ON COLUMNS") ;
				if(measure.length() > 0 && fixed.length() > 0) {
					colstrb.append("Union(CrossJoin(").append(coldimstrb).append(" , ").append(measure).append(" ) , ")
						.append("CrossJoin(").append(coldimstrb).append(" , ").append(fixed).append(" ) ")
						.append(")").append(" ON COLUMNS");
				}else if(measure.length() > 0) {
					colstrb.append("CrossJoin(").append(coldimstrb).append(" , ").append(measure).append(" ) ").append(" ON COLUMNS");
				}else if(fixed.length() > 0) {
					colstrb.append("CrossJoin(").append(coldimstrb).append(" , ").append(fixed).append(" ) ").append(" ON COLUMNS");
				}
			}else if(measure.length() > 0 || fixed.length() > 0){
				if(measure.length() > 0 && fixed.length() > 0){
					colstrb.append("Union(").append(measure).append(" , ").append(fixed).append(")").append(" ON COLUMNS");
				}else if(measure.length() > 0){
					colstrb.append(measure).append(" ON COLUMNS");
				}else if(fixed.length() > 0){
					colstrb.append(fixed).append(" ON COLUMNS");
				}
			}else {
				if(cube.getMeasure().size() > 0) {
					CubeMeasure cm = cube.getMeasure().get(0) ;
					colstrb.append(" {[Measures].[").append(cm.getName()).append("]} ").append(" ON COLUMNS");
				}
			}
		}else {
			colstrb.append(measure).append(" ON COLUMNS");
		}
		
		StringBuffer strb = new StringBuffer() , with = new StringBuffer();

		with.append("WITH ") ;
		boolean withmember = false ;
		for(ColumnProperties cp : model.getMeasures()) {
			CubeMeasure temp = null ;
			for(CubeMeasure cm:cube.getMeasure()) {
				if(cp.getDataid().equals(cm.getId())) {
					temp = cm ;
					break ;
				}
			}
			String title = cp.getTitle()+"_RE_NAME_TITLE" ;
			if("index".equals(cp.getType()) && last!=null) {
				with.append("MEMBER [Measures].[").append(title).append("] AS 'Rank(["+model.getName()+"].["+last.getName()+"].CurrentMember , ["+model.getName()+"].["+last.getName()+"].Members)' ");
				if(!StringUtils.isBlank(cp.getFormat())) {
					with.append(" , FORMAT_STRING = '"+cp.getFormat()+"'").append(" ") ;
				}
			}else if("summary".equals(cp.getType()) && last!=null) {
				String parent = processLevel(model.getProperties(), cp.getPrefix(), last.getName()) ;
				String prop = processLevel(model.getProperties(), cp.getSuffix(), last.getName()) ;
				if(StringUtils.isBlank(prop)) {
					prop = parent+".Parent" ;
				}else {
					prop = prop + ".Parent" ;
				}
				withmember = true ;
				with.append("MEMBER [Measures].[").append(title).append("] AS ");
				if(!StringUtils.isBlank(cp.getValue())) {
					switch(cp.getValue()) {
					case "prop" : 
						String name = cp.getPrefix() ;
						if(StringUtils.isBlank(name)) {
							name = last.getName() ;
						}
						with.append("'([Measures].["+temp.getName()+"], ["+model.getName()+"].["+last.getName()+"].CurrentMember"+parent+")/([Measures].["+temp.getName()+"], ["+model.getName()+"].["+name+"].CurrentMember"+prop+")' ") ;
						break;
					case "sum" : 
						with.append("'([Measures].["+temp.getName()+"], ["+model.getName()+"].["+last.getName()+"].CurrentMember"+parent+")' ") ;
						break;
					case "cus" : 
						break;
					default : break;
					}
				}else {
					with.append("'([Measures].["+temp.getName()+"], ["+model.getName()+"].["+last.getName()+"].CurrentMember"+parent+")' ") ;
				}
				if(!StringUtils.isBlank(cp.getFormat())) {
					with.append(" , FORMAT_STRING = '"+cp.getFormat()+"'").append(" ") ;
				}
			}else if("alias".equals(cp.getType())) {
				with.append("MEMBER [Measures].[").append(title).append("] AS ");
				with.append("'([Measures].["+temp.getName()+"])' ") ;
				if(!StringUtils.isBlank(cp.getFormat())) {
					with.append(" , FORMAT_STRING = '"+cp.getFormat()+"'").append(" ") ;
				}
				withmember = true ;
			}else {
				with.append("MEMBER [Measures].[").append(title).append("] AS ");
				with.append("'([Measures].["+temp.getName()+"])' ") ;
				if(!StringUtils.isBlank(cp.getFormat())) {
					with.append(" , FORMAT_STRING = '"+cp.getFormat()+"'").append(" ") ;
				}
				withmember = true ;
			}
		}
		if(withmember) {
			strb.append(with.toString()) ;
		}
		
		strb.append("SELECT ");
		if(colstrb.length() > 0) {
			strb.append(colstrb);
		}
		if(dimstrb.length() > 0 ) {
			if(colstrb.length() > 0) {
				strb.append(" , ");
			}
			strb.append(rowstrb).append(" FROM [").append(cube.getName()).append("]");
		}else {
			strb.append(" FROM [").append(cube.getName()).append("]");
		}
		//行列交换
		if(model.isExchangerw()) {
			if(strb.indexOf(" ON COLUMNS")<0 && strb.indexOf(" ON ROWS")>=0){
				strb.replace(strb.indexOf(" ON ROWS"), strb.indexOf(" ON ROWS")+" ON ROWS".length(), " ON COLUMNS") ;
			}
			if(strb.indexOf(" ON COLUMNS")>=0 && strb.indexOf(" ON ROWS")>=0){
				int colindex = strb.indexOf(" ON COLUMNS");
				int rowindex = strb.indexOf(" ON ROWS");
				strb.replace(rowindex, rowindex+" ON ROWS".length(), " ON COLUMNS") ;
				strb.replace(colindex, colindex+" ON COLUMNS".length(), " ON ROWS") ;
			}
		}
		return strb.toString();
	}
	/**
	 * 
	 * @param rows
	 * @param cp
	 * @param lastName
	 * @return
	 */
	private String processLevel(List<ColumnProperties> rows , String cp , String lastName) {
		boolean start = false ;
		StringBuffer parent = new StringBuffer() ;
		if(rows!=null && !StringUtils.isBlank(cp) && !StringUtils.isBlank(lastName)) {
			for(ColumnProperties row : rows) {
				if(start) {
					parent.append(".Parent");
				}
				if(cp.equals(row.getTitle())) {
					start = true ;
				}
				if(lastName.equals(row.getTitle())) {
					break ;
				}
			}
		}
		return parent.toString() ;
	}
	
	/*public void fillFilterValue(ReportModel model,ReportFilter filter,HttpServletRequest request ) {
		
	}*/
	
	 public void processReportFilter(Cube cube,ReportFilter filter , HttpServletRequest request) throws Exception{
		 if(filter!=null){
			 createCubeFilter(filter, cube, request);
		 }
	 }
	 
	 public void processFilter(ReportModel model ,Cube cube, HttpServletRequest request) throws Exception{
		 if(model!=null && !model.getFilters().isEmpty()){
			 for(ReportFilter filter : model.getFilters()){
				 if("range".toString().equals(filter.getValuefiltertype())){
					 
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()))) {
						 filter.setRequeststartvalue(request.getParameter(filter.getCode())) ;
					 }else {
						 filter.setRequeststartvalue(getStartValue(filter, request)) ;
					 }
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()+"_endvalue"))) {
						 filter.setRequestendvalue(request.getParameter(filter.getCode()+"_endvalue")) ;
					 }else {
						 filter.setRequestendvalue(getEndValue(filter, request)) ;
					 }
				 }else{
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()))) {
						 filter.setRequestvalue(request.getParameter(filter.getCode())) ;
						 filter.setRequestvaluename(request.getParameter(filter.getCode()));
						 if (!StringUtils.isBlank(request.getParameter(filter.getCode()+"_name"))) {
							 filter.setRequestvaluename(request.getParameter(filter.getCode()+"_name"));
						}
					 }else {
						 filter.setRequestvalue(getDefaultValue(filter, request)) ;
					 }
	        	 }
			 }
			 for(ReportFilter filter : model.getFilters()){
				 processReportFilter(cube, filter, request);
			 }
		 }
	 }
	 public ReportFilter processFilter(ReportModel model ,ReportFilter curFilter,Cube cube, HttpServletRequest request) throws Exception{
		 if(model!=null && !model.getFilters().isEmpty()){
			 for(ReportFilter filter : model.getFilters()){
				 if("range".toString().equals(filter.getValuefiltertype())){
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()+"_start"))) {
						 filter.setRequeststartvalue(request.getParameter(filter.getCode()+"_start")) ;
					 }else {
						 filter.setRequeststartvalue(getStartValue(filter, request)) ;
					 }
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()+"_end"))) {
						 filter.setRequestendvalue(request.getParameter(filter.getCode()+"_end")) ;
					 }else {
						 filter.setRequeststartvalue(getEndValue(filter, request)) ;
					 }
				 }else{
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()))) {
						 filter.setRequestvalue(request.getParameter(filter.getCode())) ;
					 }else {
						 filter.setRequestvalue(getDefaultValue(filter, request)) ;
					 }
	        	 }
			 }
			 return createCubeFilter(curFilter, cube, request);
		 }
		 return null;
	 }
	 
	 /**
	 * 获取默认的 参数
	 * @param filter
	 * @return
	 * @throws TemplateException 
	 * @throws IOException 
	 */
	public static String getDefaultValue(ReportFilter filter , HttpServletRequest request){
		String value = filter.getDefaultvalue();
		if(value!=null && value.matches("[ ]{0,}[TtMmYy]{1,}[ ]{0,}[+-]{0,1}([\\d]{0,})")){//处理动态参数的问题 ， Y表示 年 ， 如 Y+1 ， M表示 月 ， 如：M+1 ， T表示 日 ， 如 T+1 ， 例如，Y-1 = 2013 ， M-1 = 8
			value = UKTools.processParam(filter.getFormatstr() , value);
		}
		value = StringUtils.isBlank(filter.getRequestvalue()) ? value : filter.getRequestvalue();
		return value;
	}
	
	/**
	 * 获取默认的 参数
	 * @param filter
	 * @return
	 */
	public static String getStartValue(ReportFilter filter , HttpServletRequest request){
		String startValue = filter.getStartvalue();
		if(startValue!=null && startValue.matches("[ ]{0,}[TtMmYy]{1,}[ ]{0,}[+-]{0,1}([\\d]{0,})")){//处理动态参数的问题 ， Y表示 年 ， 如 Y+1 ， M表示 月 ， 如：M+1 ， T表示 日 ， 如 T+1 ， 例如，Y-1 = 2013 ， M-1 = 8
			startValue = UKTools.processParam(filter.getFormatstr() , startValue);
		}
		return StringUtils.isBlank(filter.getRequeststartvalue()) ? startValue :filter.getRequeststartvalue() ;
	}
	
	/**
	 * 获取默认的 参数
	 * @param filter
	 * @return
	 */
	public static String getEndValue(ReportFilter filter , HttpServletRequest request){
		String endValue = filter.getEndvalue();
		if(endValue!=null && endValue.matches("[ ]{0,}[TtMmYy]{1,}[ ]{0,}[+-]{0,1}([\\d]{0,})")){//处理动态参数的问题 ， Y表示 年 ， 如 Y+1 ， M表示 月 ， 如：M+1 ， T表示 日 ， 如 T+1 ， 例如，Y-1 = 2013 ， M-1 = 8
			endValue =UKTools.processParam(filter.getFormatstr() , endValue);
		}
		return StringUtils.isBlank(filter.getRequestendvalue())? endValue : filter.getRequestendvalue();
	}
	
	/**
	 * 生成过滤器自动获取数据SQL
	 * @param model
	 * @param cube
	 * @param request
	 * @param queryLog
	 * @return
	 * @throws Exception 
	 */
	public ReportFilter createCubeFilter(ReportFilter filter,Cube cube,HttpServletRequest request) throws Exception{
		String orgi = UKDataContext.SYSTEM_ORGI;
		if(FilterConValueType.AUTO.toString().equals(filter.getConvalue()) && UKDataContext.FilterModelType.SIGSEL.toString().equals(filter.getModeltype()) ) {
			Map<String,MetadataTable> tableMap = new HashMap<>();
			Map<String,String> tableIndexMap = new HashMap<>();
			Map<String,TableProperties> tableppyMap = new HashMap<>();
			int index = 1;
			if(cube!=null) {
				for(CubeMetadata cm:cube.getMetadata()) {
					tableMap.put(cm.getTb().getId(), cm.getTb());
					tableIndexMap.put(cm.getTb().getId(), "a"+index);
					List<TableProperties> cmtableProperties = tablePropertiesRes.findByDbtableid(cm.getTb().getId());
					for(TableProperties tableproperty:cmtableProperties) {
						tableppyMap.put(tableproperty.getId(),tableproperty);
					}
					index++;
				}
			}else {
				for(MetadataTable cm:metadataRes.findByOrgi(orgi)) {
					tableMap.put(cm.getId(), cm);
					tableIndexMap.put(cm.getId(), "a"+index);
					for(TableProperties tableproperty:cm.getTableproperty()) {
						tableppyMap.put(tableproperty.getId(),tableproperty);
					}
					index++;
				}
			}
			MetadataTable table = (MetadataTable)tableMap.get(filter.getFktableid());
			TableProperties keyfield = (TableProperties)tableppyMap.get(filter.getKeyfield());
			TableProperties valuefield = (TableProperties)tableppyMap.get(filter.getValuefield());
			if(table !=null && keyfield!=null && valuefield!=null && StringUtils.isNotBlank(filter.getFktableid()) && StringUtils.isNotBlank(filter.getKeyfield()) && StringUtils.isNotBlank(filter.getValuefield())) {
				StringBuffer strb = new StringBuffer() ;
				strb.append("select ");
				//要查询的表名以及左连接
				StringBuffer mainTable = new StringBuffer(" from ");
				
				mainTable.append(table.getTablename()).append(" as ").append(tableIndexMap.get(filter.getFktableid()));
				//要查询的列名
				StringBuffer columns = new StringBuffer() ;
				columns.append(tableIndexMap.get(filter.getFktableid())).append(".").append(keyfield.getFieldname()).append(" KEYVAL,");
				columns.append(tableIndexMap.get(filter.getFktableid())).append(".").append(valuefield.getFieldname()).append(" VAL");
				
				StringBuffer wherecon = new StringBuffer(" 1=1 ");
				wherecon.append(" and "+tableIndexMap.get(filter.getFktableid())+".orgi = ").append("'").append(filter.getOrgi()).append("' ");
				if (table.getTableproperty()!=null && table.getTableproperty().size() > 0) {
					for(TableProperties t : table.getTableproperty()){
						if (!StringUtils.isBlank(t.getFieldname()) && t.getFieldname().toLowerCase().equals("datastatus")) {
							wherecon.append(" and datastatus='0' ");
							break;
						}
						//数据权限
						if (!StringUtils.isBlank(t.getFieldname()) && t.getFieldname().toLowerCase().equals("organ") && filter.isDataauth()) {
							User user = getUser(request);
							List<String> organList = CallCenterUtils.getExistOrgan(user);
							if (!user.isSuperuser()) {
								if (organList != null && organList.size() > 0) {
									for(int i=0;i<organList.size();i++){
										if (i==0) {
											wherecon.append(" and ").append("organ").append(" in ('").append(organList.get(i)).append("'");
										}else{
											wherecon.append(",'").append(organList.get(i)).append("'");
										}
										if (i==organList.size()-1) {
											wherecon.append(") ");
										}
									}
								}else{
									wherecon.append(" and ").append("organ").append(" ='").append(UKDataContext.UKEFU_SYSTEM_NO_DAT).append("' ");
								}
							}
						}
					}
				}
				StringBuffer leftjoin = new StringBuffer();
				//查询所有级联上级
				List<ReportFilter> rfList = reportFilterRes.findByCascadeidAndOrgi(filter.getId(), filter.getOrgi());
				//字典项
				//if(filter.isIsdic() && (rfList==null || rfList.isEmpty())) {
				if(filter.isIsdic()){
					leftjoin.append(" left join uk_sysdic as b1 on b1.id = ").append(tableIndexMap.get(filter.getFktableid())).append(".id ");
					wherecon.append(" and b1.dicid = (select id from uk_sysdic where code = ").append("'").append(filter.getDiccode()).append("') ");
					if(rfList == null || rfList.isEmpty()) {
						wherecon.append(" and b1.parentid = (select id from uk_sysdic where code = ").append("'").append(filter.getDiccode()).append("') ");
					}
				}
				if(rfList !=null && !rfList.isEmpty()) {
					for(ReportFilter f : rfList){
						 if("range".toString().equals(f.getValuefiltertype())){
							 f.setRequeststartvalue(request.getParameter(f.getCode()+"_start")) ;
							 f.setRequestendvalue(request.getParameter(f.getCode()+"_end")) ;
						 }else{
							 f.setRequestvalue(request.getParameter(f.getCode())) ;
			        	 }
					 }
					Map<String,String> tablefilterMap = new HashMap<>();
					for(ReportFilter f : rfList){
						tablefilterMap.put(f.getFktableid()+f.getId(), "c"+index);
						index++;
					}
					for(ReportFilter f:rfList) {
						leftjoin.append(" left join ").append((tableMap.get(f.getFktableid()).getTablename())).append(" as ").append(tablefilterMap.get(f.getFktableid()+f.getId())).append(" on ").append(tablefilterMap.get(f.getFktableid()+f.getId())).append(".").append((tableppyMap.get(f.getKeyfield()).getFieldname())).append(" = ").append(tableIndexMap.get(filter.getFktableid())).append(".").append(f.getTableproperty().getFieldname());
						wherecon.append(" and ").append(tablefilterMap.get(f.getFktableid()+f.getId())).append(".").append((tableppyMap.get(f.getKeyfield()).getFieldname())).append(" = '").append(getDefaultValue(f, request)).append("' ");
					}
				}
				strb.append(columns).append(mainTable).append(leftjoin);
				if(wherecon.length()>0){
					strb.append(" where ").append(wherecon);
				}
				List<Map<String, Object>> result = jdbcTemplate.queryForList(strb.toString());
				filter.setSqlresult(result);
				return filter;
			}
		}
		return filter;
	}
	
	/**
	 * 填充报表过滤器的值以及下拉值 
	 * @param report
	 * @return
	 * @throws Exception 
	 */
	public List<ReportFilter> fillReportFilterData(List<ReportFilter> reportFilterList,HttpServletRequest request) throws Exception {
		ReportModel model = new ReportModel();
		model.setFilters(reportFilterList);
		processFilter(model, null, request);
		return model.getFilters();
	}
	
	
	private ReportData getESCubeReportData(ReportModel model , Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap)  throws Exception{
		boolean isTol = false;//是否合计
		//过滤器取值
		if(model!=null && !model.getFilters().isEmpty()){
			 for(ReportFilter filter : model.getFilters()){
				 if("range".toString().equals(filter.getValuefiltertype())){
					 
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()))) {
						 filter.setRequeststartvalue(request.getParameter(filter.getCode())) ;
					 }else {
						 filter.setRequeststartvalue(getStartValue(filter, request)) ;
					 }
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()+"_endvalue"))) {
						 filter.setRequestendvalue(request.getParameter(filter.getCode()+"_endvalue")) ;
					 }else {
						 filter.setRequestendvalue(getEndValue(filter, request)) ;
					 }
				 }else{
					 if(!StringUtils.isBlank(request.getParameter(filter.getCode()))) {
						 filter.setRequestvalue(request.getParameter(filter.getCode())) ;
					 }else {
						 filter.setRequestvalue(getDefaultValue(filter, request)) ;
					 }
	        	 }
			 }
		 }
		
		User user = (User) request.getSession(true).getAttribute(UKDataContext.USER_SESSION_NAME)  ;
		String orgi = user != null ? user.getOrgi():UKDataContext.SYSTEM_ORGI;
		BoolQueryBuilder query = QueryBuilders.boolQuery()  ;
		query.must(QueryBuilders.termQuery("orgi" , orgi)) ;
		
		//确定主表
		CubeMetadata mainMetaData = null ;
		for(CubeMetadata cm:cube.getMetadata()) {
			if("0".equals(cm.getMtype())) {
				mainMetaData = cm ;
			}
		}
		SearchRequestBuilder searchBuilder = null;
		if (mainMetaData != null) {
			List<ReportFilter> filters = null;
			if(model.getFilters().size() > 0){
				filters = model.getFilters() ;
			}
			if(filters != null && filters.size()>0){//过滤器生成query
				for(ReportFilter filter : filters){
					if(!StringUtils.isBlank(filter.getDataname())){
						String requestParamValue = filter.getRequestvalue() ;
						if("compare".equals(filter.getValuefiltertype()) || StringUtils.isBlank(filter.getValuefiltertype())){ //比较
							if(!StringUtils.isBlank(requestParamValue)){
								if("q".equals(filter.getCode())){
									query.must(new QueryStringQueryBuilder(requestParamValue).defaultOperator(Operator.AND)) ;
								}else if(filter.getTableid()!=null && filter.getTableid().equals(mainMetaData.getTb().getId())){
									query.must(new QueryStringQueryBuilder(requestParamValue).field(filter.getCode()).defaultOperator(Operator.AND)) ;
								}
							}
						}else if("not".equals(filter.getValuefiltertype())){ //比较
							if(!StringUtils.isBlank(requestParamValue)){
								if("q".equals(filter.getCode())){
									query.mustNot(new QueryStringQueryBuilder(requestParamValue).defaultOperator(Operator.AND)) ;
								}else if(filter.getTableid()!=null && filter.getTableid().equals(mainMetaData.getTb().getId())){
									query.mustNot(new QueryStringQueryBuilder(requestParamValue).field(filter.getTableproperty().getFieldname()).defaultOperator(Operator.AND)) ;
								}
							}
						}else if(filter.getTableid()!=null && filter.getTableid().equals(mainMetaData.getTb().getId())){		//范围
							RangeQueryBuilder builder = null ; 
							String requestStartValue = filter.getRequeststartvalue() ;
							String requestEndValue = filter.getRequestendvalue() ;
							if(!StringUtils.isBlank(requestEndValue)){
								builder = QueryBuilders.rangeQuery(filter.getCode()).from(requestStartValue) ;
							}
							if(!StringUtils.isBlank(requestEndValue)){
								if(builder!=null){
									builder = builder.to(requestEndValue) ;
								}else{
									builder = QueryBuilders.rangeQuery(filter.getCode()).to(requestEndValue) ;
								}
							}
						}
					}
				}
			}
			
			searchBuilder = UKDataContext.getTemplet().getClient().prepareSearch(UKDataContext.CALLOUT_INDEX).setTypes(mainMetaData.getTb().getTablename()) ;
			searchBuilder.setQuery(query).setFrom(0).setSize(10000) ;
			
			@SuppressWarnings("rawtypes")
			AggregationBuilder termsBuilder = null,subTermsBuilder = null;
			
			//维度
			CubeLevel last = null ; 
			if(!model.getProperties().isEmpty()) {
				ColumnProperties cp = model.getProperties().get(model.getProperties().size()-1);
				all:for(Dimension d:cube.getDimension()) {
					for(CubeLevel cl:d.getCubeLevel()) {
						if(cp.getDataid().equals(cl.getId())) {
							last = cl ;
							if(cp.isUnresize()) {
								isTol = true;
							}
							break all;
						}
					}
				}
			}
			if (last != null ) {
				Order order = Terms.Order.term(true);
				if(termsBuilder == null){
					if("datetime".equalsIgnoreCase(last.getType()) || "date".equalsIgnoreCase(last.getType())){
						termsBuilder = subTermsBuilder = AggregationBuilders.dateHistogram(last.getName()).field(last.getColumname()).format(!StringUtils.isBlank(last.getFormatstr()) ? last.getFormatstr() : "yyyy-MM-dd") ;
					}else{
						termsBuilder = subTermsBuilder = AggregationBuilders.terms(last.getName()).field(last.getColumname()).order(order).size(0) ; //翻页支持
					}
				}else{
					subTermsBuilder.subAggregation(subTermsBuilder = AggregationBuilders.terms(last.getName()).field(last.getColumname()).order(order).size(0)) ;
				}
			}
			
			
			//指标生成query
			if(model.getMeasures().size()>0){
				for(ColumnProperties cp : model.getMeasures()){
					if(cp!=null){
						CubeMeasure cm = null;
						List<CubeMeasure> cubemeasureList =cube.getMeasure();
						if (cubemeasureList != null &&cubemeasureList.size() > 0) {
							for(CubeMeasure cubemeasure : cubemeasureList ){
								if (cubemeasure.getId().equals(cp.getDataid())) {
									cm = cubemeasure;
									break;
								}
							}
						}
						if (cm != null) {

							if("datetime".equals(cp.getDatatypename()) || "date".equals(cp.getDatatypename())){
								searchBuilder.addAggregation(AggregationBuilders.dateHistogram(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "yyyy-MM-dd")) ;
							}else if("number".equals(cp.getDatatypename()) || "Long".equals(cp.getDatatypename())){ //
								if(!StringUtils.isBlank(cm.getAggregator())){
									switch(cm.getAggregator()){
										case "count" : 	searchBuilder.addAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ; break ;
										case "distinct-count" : 	searchBuilder.addAggregation(AggregationBuilders.cardinality(cp.getTitle()).field(cm.getColumname())) ; break ;
										case "sum" : 	searchBuilder.addAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "min" : 	searchBuilder.addAggregation(AggregationBuilders.min(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "max" : 	searchBuilder.addAggregation(AggregationBuilders.max(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "avg" : 	searchBuilder.addAggregation(AggregationBuilders.avg(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###.##")) ; break ;
									}
								}else{
									searchBuilder.addAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ;
								}
							}else{
								if(!StringUtils.isBlank(cm.getAggregator())){
									switch(cm.getAggregator()){
										case "count" : 	searchBuilder.addAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ; break ;
										case "distinct-count" : 	searchBuilder.addAggregation(AggregationBuilders.cardinality(cp.getTitle()).field(cm.getColumname())) ; break ;
										case "sum" : 	searchBuilder.addAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "min" : 	searchBuilder.addAggregation(AggregationBuilders.min(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "max" : 	searchBuilder.addAggregation(AggregationBuilders.max(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
										case "avg" : 	searchBuilder.addAggregation(AggregationBuilders.avg(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###.##")) ; break ;
									}
								}else{
									searchBuilder.addAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ;
								}
							}
						
							if(termsBuilder == null){}else{
								if("datetime".equals(cp.getDatatypename()) || "date".equals(cp.getDatatypename())){
									subTermsBuilder.subAggregation(AggregationBuilders.dateHistogram(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "yyyy-MM-dd")) ;
								}else if("number".equals(cp.getDatatypename()) || "Long".equals(cp.getDatatypename())){ //
									if(!StringUtils.isBlank(cm.getAggregator())){
										switch(cm.getAggregator()){
											case "count" : 	subTermsBuilder.subAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ; break ;
											case "distinct-count" : 	subTermsBuilder.subAggregation(AggregationBuilders.cardinality(cp.getTitle()).field(cm.getColumname())) ; break ;
											case "sum" : 	subTermsBuilder.subAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "min" : 	subTermsBuilder.subAggregation(AggregationBuilders.min(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "max" : 	subTermsBuilder.subAggregation(AggregationBuilders.max(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "avg" : 	subTermsBuilder.subAggregation(AggregationBuilders.avg(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###.##")) ; break ;
										}
									}else{
										subTermsBuilder.subAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ;
									}
								}else{
									if(!StringUtils.isBlank(cm.getAggregator())){
										switch(cm.getAggregator()){
											case "count" : 	subTermsBuilder.subAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ; break ;
											case "distinct-count" : 	subTermsBuilder.subAggregation(AggregationBuilders.cardinality(cp.getTitle()).field(cm.getColumname())) ; break ;
											case "sum" : 	subTermsBuilder.subAggregation(AggregationBuilders.sum(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "min" : 	subTermsBuilder.subAggregation(AggregationBuilders.min(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "max" : 	subTermsBuilder.subAggregation(AggregationBuilders.max(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###")) ; break ;
											case "avg" : 	subTermsBuilder.subAggregation(AggregationBuilders.avg(cp.getTitle()).field(cm.getColumname()).format(!StringUtils.isBlank(cp.getFormat()) ? cp.getFormat() : "###.##")) ; break ;
										}
									}else{
										subTermsBuilder.subAggregation(AggregationBuilders.count(cp.getTitle()).field(cm.getColumname())) ;
									}
								}
							}
						}
					}
					
				}
			}
			if(termsBuilder!=null){
				searchBuilder.addAggregation(termsBuilder) ;
			}
		}
		
		ReportData reportData = new ESCubeService().execute(searchBuilder,model.getMeasures(),isTol);
		
		
		if("true".equals(model.getIsloadfulldata())) {
			int p = Integer
					.parseInt(request.getParameter("p") != null
							&& request.getParameter("p").matches(
									"[\\d]{1,}") ? request
							.getParameter("p") : "1");
			int ps = Integer
					.parseInt(request.getParameter("ps") != null
							&& request.getParameter("ps").matches(
									"[\\d]{1,}") ? request
							.getParameter("ps") : "0");
			if (p < 1) {
				p = 1;
			}
			if (ps < 1) {
				ps = model.getPagesize()>0 ? model.getPagesize() : 20;
			}
			reportData.setPage(p);
			reportData.setPageSize(ps);
		}
		
		return reportData;
	}
	
	/**
	 * 数据表
	 * @param model
	 * @param cube
	 * @param request
	 * @param parseParam
	 * @param semap
	 * @return
	 * @throws Exception
	 */
	private ReportData getSqlCubeReportData(ReportModel model , Cube cube,HttpServletRequest request , boolean parseParam, HashMap<String,String> semap)  throws Exception{
		processFilter(model, cube, request);
		String orgi = UKDataContext.SYSTEM_ORGI;

		Template modeltp =  templateRes.findByIdAndOrgi(model.getTempletid(),orgi);

		boolean isTable = modeltp!=null&&"数据表".equals(modeltp.getName());
		
		SqlCubeService sqlCubeService= new SqlCubeService(druidDataSource);
		cube.setSql(createCubeSQL(model, cube, request , true,semap)) ;
		ReportData reportDataTotal = sqlCubeService.execute(cube.getSql() , model.getMeasures()) ;
		int p = 0;
		int ps = 0;
		
		if(!model.isSearchexp()){
			
			if("true".equals(model.getIsloadfulldata()) && model.getPagesize()>0 && !StringUtils.isBlank(cube.getSql())) {
				StringBuffer sqlstr= new StringBuffer(cube.getSql());
				p = Integer
						.parseInt(request.getParameter("p") != null
						&& request.getParameter("p").matches(
								"[\\d]{1,}") ? request
										.getParameter("p") : "1");
				ps = Integer
						.parseInt(request.getParameter("ps") != null
						&& request.getParameter("ps").matches(
								"[\\d]{1,}") ? request
										.getParameter("ps") : "0");
				if (p < 1) {
					p = 1;
				}
				if (ps < 1) {
					ps = model.getPagesize()>0 ? model.getPagesize() : 20;
				}
				sqlstr.append(" LIMIT "+ (p-1)*ps+","+ps);
				cube.setSql(sqlstr.toString());
			}
		}
		Map<String,Object> tplValuesMap = new HashMap<>();
		tplValuesMap.put("reportModel", model);
		tplValuesMap.put("cube", cube);
		tplValuesMap.put("name", model.getName());
		tplValuesMap.put("istable", isTable);
		ReportData reportData = sqlCubeService.execute(cube.getSql() , model.getMeasures()) ;
		reportData.setTotal(reportDataTotal.getData().size());
		if("true".equals(model.getIsloadfulldata())) {
			reportData.setPage(p);
			reportData.setPageSize(ps);
		}
		
		return reportData;
	}
	
	/**
	 * 
	 * @param id
	 * @param orgi
	 * @return
	 */
	private ReportModel getModel(String id,String orgi) {
		ReportModelRepository reportModelRes = UKDataContext.getContext().getBean(ReportModelRepository.class) ;
		ColumnPropertiesRepository columnPropertiesRepository = UKDataContext.getContext().getBean(ColumnPropertiesRepository.class) ;
		ReportFilterRepository reportFilterRepository = UKDataContext.getContext().getBean(ReportFilterRepository.class) ;
		ReportModel model = reportModelRes.findByIdAndOrgi(id, orgi);
		if (model != null) {
			model.setProperties(
					columnPropertiesRepository.findByModelidAndCurOrderBySortindexAsc(model.getId(), "field"));
			model.setColproperties(
					columnPropertiesRepository.findByModelidAndCurOrderBySortindexAsc(model.getId(), "cfield"));
			model.setMeasures(
					columnPropertiesRepository.findByModelidAndCurOrderBySortindexAsc(model.getId(), "measure"));
			List<ReportFilter> listFilters = reportFilterRepository.findByModelidOrderBySortindexAsc(model.getId());
			if(!listFilters.isEmpty()) {
				for(ReportFilter rf:listFilters) {
					if(!StringUtils.isBlank(rf.getCascadeid())) {
						rf.setChildFilter(reportFilterRepository.findByIdAndOrgi(rf.getCascadeid(), orgi));
					}
				}
			}
			model.setFilters(listFilters);
		}
		return model;
	}
	/**
	 * 查找指标配置
	 * @param name
	 * @param properties
	 * @return
	 */
	private ColumnProperties getColumnProperties(String name , List<ColumnProperties> properties) {
		ColumnProperties columnProperties = null;
		if(!StringUtils.isBlank(name)) {
			for(ColumnProperties temp : properties) {
				if(name.equals(temp.getTitle())) {
					columnProperties = temp ; break ;
				}
			}
		}
		return columnProperties ;
	}
	
	/**
	 * 查找指标配置
	 * @param name
	 * @param properties
	 * @return
	 */
	private List<List<ValueData>> processMeasure(List<List<ValueData>> data, List<ColumnProperties> properties) {
		for(List<ValueData> valueDataList : data) {
			for(int i=0 ; i<valueDataList.size() ; i++) {
				ValueData valueData = valueDataList.get(i) ;
				ColumnProperties columnProperties = getColumnProperties(valueData.getName() , properties) ;
				if(columnProperties!=null && columnProperties.isLinkdata()) {
					if(columnProperties.getLinktitlecolspan() > 0) {
						ValueData titleValueData =  valueData.clone();
						titleValueData.setForamatValue(titleValueData.getName());
						titleValueData.setColspan(columnProperties.getLinktitlecolspan());
						valueDataList.add(i, titleValueData);
						i = i+1 ;
					}
					if(columnProperties.getLinkmeacolspan() > 0) {
						valueData.setColspan(columnProperties.getLinkmeacolspan());
					}
				}
			}
		}
		return data ;
	}
	
	private User getUser(HttpServletRequest request){
		User user = (User) request.getSession(true).getAttribute(UKDataContext.USER_SESSION_NAME)  ;
		if(user==null){
			String authorization = request.getHeader("authorization") ;
			if(StringUtils.isBlank(authorization) && request.getCookies()!=null){
				for(Cookie cookie : request.getCookies()){
					if(cookie.getName().equals("authorization")){
						authorization = cookie.getValue() ; break ;
					}
				}
			}
			if(!StringUtils.isBlank(authorization)){
				user = (User) CacheHelper.getApiUserCacheBean().getCacheObject(authorization, UKDataContext.SYSTEM_ORGI) ;
			}
			if(user==null){
				user = new User();
				user.setId(UKTools.getContextID(request.getSession().getId())) ;
				user.setUsername(UKDataContext.GUEST_USER+"_"+UKTools.genIDByKey(user.getId())) ;
				user.setOrgi(UKDataContext.SYSTEM_ORGI);
				user.setSessionid(user.getId()) ;
			}
		}else{
			user.setSessionid(user.getId()) ;
		}
		return user ;
	}
}